{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# xlwt 工具包可以对 xls 文件进行写操作\n",
    "# xlwt 的命名是 excel writer 简写，很容易记忆\n",
    "import xlwt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 如果本地没有安装工具包，请去掉“#”，再执行\n",
    "# !pip install -i https://mirrors.aliyun.com/pypi/simple/ xlwt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 定义workbook\n",
    "workbook = xlwt.Workbook(encoding='utf-8')\n",
    "\n",
    "# 添加sheet，这个sheet的名字叫 '样式'\n",
    "sheet = workbook.add_sheet('样式')  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建一个样式对象\n",
    "style = xlwt.XFStyle()  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue>Excel颜色索引对照表</font>**<br/>\n",
    "<img src=\"images/Excel颜色对照表2.png\" align=\"left\"></img>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建一个背景图案对象\n",
    "pattern = xlwt.Pattern()\n",
    "# 表示是否设置填充，默认 NO_PATTERN(值为0)，不设置任何填充。还有一个选项SOLID_PATTERN(1)，设置填充。\n",
    "pattern.pattern = xlwt.Pattern.SOLID_PATTERN\n",
    "# 设置填充的前景色赋值为黄色 ，参考：Excel颜色索引对照表  \n",
    "pattern.pattern_fore_colour = 5\n",
    "# 设置填充的背景色为蓝色，参考：Excel颜色索引对照表 \n",
    "pattern.pattern_back_colour = 4\n",
    "\n",
    "# 把背景图案加到样式里去\n",
    "style.pattern = pattern"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue>Excel单元格图案设置</font>**<br/>\n",
    "<img src=\"images/Excel单元格图案.jpg\" align=\"left\"></img>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建字体\n",
    "font = xlwt.Font()\n",
    "# 设置字体\n",
    "font.name = '微软雅黑'\n",
    "# 设置字体颜色为红色，参考：Excel颜色索引对照表  \n",
    "font.colour_index = 2 \n",
    "# 字体大小，16为字号，20为衡量单位\n",
    "font.height = 20 * 16\n",
    "\n",
    "# 把字体加到样式里去\n",
    "style.font = font"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue>Excel单元格字体设置</font>**<br/>\n",
    "<img src=\"images/Excel单元格字体.jpg\" align=\"left\"></img>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建对齐\n",
    "alignment  = xlwt.Alignment()\n",
    "\"\"\"\n",
    "# 水平对齐\n",
    "0: HORZ_GENERAL\n",
    "1: HORZ_LEFT\n",
    "2: HORZ_CENTER\n",
    "3: HORZ_RIGHT\n",
    "4: HORZ_FILLED\n",
    "5: HORZ_JUSTIFIED # BIFF4-BIFF8X\n",
    "6: HORZ_CENTER_ACROSS_SEL # Centred across selection (BIFF4-BIFF8X)\n",
    "7: HORZ_DISTRIBUTED # Distributed (BIFF8X)\n",
    "\"\"\"\n",
    "alignment.horz = 2 # 居中\n",
    "\"\"\"\n",
    "垂直对齐\n",
    "0: VERT_TOP\n",
    "1: VERT_CENTER\n",
    "2: VERT_BOTTOM\n",
    "3: VERT_JUSTIFIED\n",
    "4: VERT_DISTRIBUTED\n",
    "\"\"\"\n",
    "alignment.vert = 1\n",
    "# 设置自动换行\n",
    "alignment.wrap = 1\n",
    "\n",
    "# 把对齐加到样式里去\n",
    "style.alignment = alignment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue>Excel单元格对齐设置</font>**<br/>\n",
    "<img src=\"images/Excel单元格对齐.jpg\" align=\"left\"></img>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建边框\n",
    "borders = xlwt.Borders()\n",
    "# 设置上下左右边框为虚线\n",
    "# DASHED为虚线 、NO_LINE 为没有边框、THIN 为实线\n",
    "borders.top = xlwt.Borders.DASHED \n",
    "borders.bottom = xlwt.Borders.DASHED \n",
    "borders.left = xlwt.Borders.DASHED  \n",
    "borders.right = xlwt.Borders.DASHED \n",
    "\n",
    "# 设置上下左右边框为颜色为蓝色\n",
    "borders.left_colour = 4\n",
    "borders.right_colour = 4\n",
    "borders.top_colour = 4\n",
    "borders.bottom_colour = 4\n",
    "\n",
    "# 设置上下左右边框线型和粗细\n",
    "borders.left = 1\n",
    "borders.right = 1\n",
    "borders.top = 1\n",
    "borders.bottom = 1\n",
    "# 1:细实线 2:小粗实线 3:细虚线 4:中细虚线 5:大粗实线 6:双线 7:细点虚线\n",
    "# 8:大粗虚线 9:细点划线 10:粗点划线 11:细双点划线 12:粗双点划线 13斜点划线\n",
    "\n",
    "# 把边框加到样式里去\n",
    "style.borders = borders"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue>Excel单元格边框设置</font>**<br/>\n",
    "<img src=\"images/Excel单元格边框.jpg\" align=\"left\"></img>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 什么是“随机数”？\n",
    "# 就是在一定范围内随机产生的数\n",
    "# 加入随机数工具包\n",
    "import random\n",
    "\n",
    "# 在1~50范围中随机生成一个整数（数据类型为整型）\n",
    "print(random.randint(1,50))\n",
    "\n",
    "# 在0~1范围中随机生成一个小数（数据类型为浮点型）\n",
    "print(random.random())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 演示随机数的生成\n",
    "for i in range(5):\n",
    "    print(random.randint(1,50))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 加入随机数工具包\n",
    "import random\n",
    "\n",
    "# ** 注意** 这个有 2 个 for 循环\n",
    "# 这里是循环行（0 - 2）\n",
    "for row in range(3):\n",
    "    # 这里循环列（0 - 4）\n",
    "    for column in range(5):\n",
    "        # 随机生成最小值为1，最大值为50的整数\n",
    "        value = random.randint(1,50)\n",
    "        # 写入数据，第row行，第column列，具体内容是随机生成的数值\n",
    "        sheet.write(row, column, value, style)\n",
    "        \n",
    "# 保存 Excel 到指定的位置和文件名,如果只有文件名，则默认保存代码同一个路径下面。\n",
    "workbook.save('./files/Excel_样式处理.xls')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python总结"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue size=4> 1、面向对象编程的进一步了解</font>**\n",
    "\n",
    "<font color=blue size=4> 我们编程写的代码，基本上和实际 Excel 的设置界面相对应 </font>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue size=4> 2、行Row 和 列Column 双循环的使用</font>**\n",
    "\n",
    "<font color=red size=4> for row in range(3): </font>\n",
    "\n",
    "<font color=red size=4> &nbsp;&nbsp;&nbsp;for column in range(5): </font>\n",
    "\n",
    "<font color=blue size=4> 其实还可以更多循环的使用，但是需要注意：<font color=red>更多的循环会导致代码理解困难</font>，所以不建议过多循环，具体多少合适，还得看具体情况而定</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 完整代码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# xlwt 工具包可以对 xls 文件进行写操作\n",
    "# xlwt 的命名是 excel writer 简写，很容易记忆\n",
    "import xlwt\n",
    "# 加入随机数工具包\n",
    "import random\n",
    "\n",
    "# 定义workbook\n",
    "workbook = xlwt.Workbook(encoding='utf-8')\n",
    "\n",
    "# 添加sheet，这个sheet的名字叫 '样式'\n",
    "sheet = workbook.add_sheet('样式')  \n",
    "\n",
    "# 创建一个样式对象\n",
    "style = xlwt.XFStyle()  \n",
    "\n",
    "# 创建一个背景图案对象\n",
    "pattern = xlwt.Pattern()\n",
    "pattern.pattern = xlwt.Pattern.SOLID_PATTERN\n",
    "pattern.pattern_fore_colour = 5\n",
    "pattern.pattern_back_colour = 4\n",
    "\n",
    "# 把背景图案加到样式里去\n",
    "style.pattern = pattern\n",
    "\n",
    "# 创建字体\n",
    "font = xlwt.Font()\n",
    "font.name = '微软雅黑'\n",
    "font.colour_index = 2 \n",
    "font.height = 20 * 16\n",
    "\n",
    "# 把字体加到样式里去\n",
    "style.font = font\n",
    "\n",
    "# 创建对齐\n",
    "alignment  = xlwt.Alignment()\n",
    "alignment.horz = 2 # 居中\n",
    "alignment.vert = 1\n",
    "# 设置自动换行\n",
    "alignment.wrap = 1\n",
    "\n",
    "# 把对齐加到样式里去\n",
    "style.alignment = alignment\n",
    "\n",
    "# 创建边框\n",
    "borders = xlwt.Borders()\n",
    "# 设置上下左右边框为虚线\n",
    "# DASHED为虚线 、NO_LINE 为没有边框、THIN 为实线\n",
    "borders.top = xlwt.Borders.DASHED \n",
    "borders.bottom = xlwt.Borders.DASHED \n",
    "borders.left = xlwt.Borders.DASHED  \n",
    "borders.right = xlwt.Borders.DASHED \n",
    "\n",
    "# 设置上下左右边框为颜色为蓝色\n",
    "borders.left_colour = 4\n",
    "borders.right_colour = 4\n",
    "borders.top_colour = 4\n",
    "borders.bottom_colour = 4\n",
    "\n",
    "# 设置上下左右边框线型和粗细\n",
    "borders.left = 1\n",
    "borders.right = 1\n",
    "borders.top = 1\n",
    "borders.bottom = 1\n",
    "\n",
    "# 把边框加到样式里去\n",
    "style.borders = borders\n",
    "\n",
    "\n",
    "for row in range(3):\n",
    "    for column in range(5):\n",
    "        # 随机生成最小值为1，最大值为50的整数\n",
    "        value = random.randint(1,50)\n",
    "        # 写入数据，第row行，第column列，具体内容是随机生成的数值\n",
    "        sheet.write(row, column, value, style)\n",
    "        \n",
    "# 保存 Excel 到指定的位置和文件名,如果只有文件名，则默认保存代码同一个路径下面。\n",
    "workbook.save('./files/Excel_样式处理.xls')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 思考题"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 相对之前，代码行数变多了，有什么办法优化？"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color=blue size=4> 其实我们是可以把以上的代码拆为两部分，然后将一大部分代码放到另外一个文件中，这里的代码就自然减少了，\n",
    "解决这个问题对于初学者来说是比较难的，如果大家不会也很正常，但是没有关系，您可以先了解。  </font>\n",
    "\n",
    "<font color=blue size=4> 1、首先，我创建一个 xlwt_ext.py 文件（扩展名 py 是 Python 的简写），将一部分代码放过去</font>\n",
    "\n",
    "<font color=blue size=4> 2、以下代码 import xlwt_ext 后，代码就简单多了</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from xlwt_ext import create_style\n",
    "# 加入随机数工具包\n",
    "import random\n",
    "\n",
    "# 定义workbook\n",
    "workbook = xlwt.Workbook(encoding='utf-8')\n",
    "\n",
    "# 添加sheet，这个sheet的名字叫 '样式'\n",
    "sheet = workbook.add_sheet('样式')\n",
    "\n",
    "# 创建一个样式（这里的代码全部放到  xlwt_ext.py 文件中）\n",
    "style = create_style()\n",
    "\n",
    "for row in range(3):\n",
    "    for column in range(5):\n",
    "        # 随机生成最小值为1，最大值为50的整数\n",
    "        value = random.randint(1,50)\n",
    "        # 写入数据，第row行，第column列，具体内容是随机生成的数值\n",
    "        sheet.write(row, column, value, style)\n",
    "        \n",
    "# 保存 Excel 到指定的位置和文件名,如果只有文件名，则默认保存代码同一个路径下面。\n",
    "workbook.save('./files/Excel_样式处理2.xls')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color=blue size=4>这还不是最优的，未来还可以更优，如果大家头还没有晕，不妨再思考一下。</font>\n",
    "    \n",
    " <font color=blue size=4>友情提醒一下，create_style 函数只能创建一个样式，无法更换颜色、字体</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 常见错误"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " <font color=blue size=4>以下是初学者容易犯的错误，而且有些问题非常难发现，大家可以运行试试</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# √ 正确的语法\n",
    "for i in range(1, 4):\n",
    "    print(\"i =\",i)\n",
    "    print(\"i×i =\",i * i) # 这个 i * i 就是是 i × i"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ✖ 错误的语法\n",
    "for i in range(1，4): # 逗号是全角，要改为半角，很难看出问题，还好有错误的提示\n",
    "    print(\"i =\",i)\n",
    "    print(\"i×i =\",i * i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ✖ 错误的语法\n",
    "for i in range(1, 4):\n",
    "    print(“i =”,i)  # 引号是全角“”，要改为半角 \"\"\n",
    "    print(“i×i =”,i * i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ✖ 错误的语法\n",
    "for i in range(1, 4):\n",
    "    print（\"i =\",i）  # 括号是全角，要改为半角\n",
    "    print（\"i×i =\",i * i）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ✖ 错误的语法\n",
    "for i in range(1, 4):\n",
    "print(\"i =\",i) # 不能和for循环对齐，要往后缩进\n",
    "print(\"i×i =\",i * i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ✖ 错误的语法\n",
    "for i in range(1, 4):\n",
    "    print(\"i =\",i)\n",
    "     print(\"i×i =\",i * i) # 2 个 print 没有对齐"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
